Excel VBA to Get Unique Values from Column (4 Examples)

We will use the following dataset to get unique values from a column. The dataset contains some products in column B and the country where the products will be delivered to in column C. We want to get the unique countries from column C to column E.

Method 1 – Find Exclusive Values from Columns with Excel VBA

Let’s extract the unique values from column C and put those values in column E using Excel VBA Macros.

Steps:

Fo to the Developer tab from the ribbon.Click on Visual Basic to open the Visual Basic Editor. Or, press Alt + F11 to open the Visual Basic Editor.

You can also just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.

Click on Module from the Insert drop-down menu bar. This will create a Module in your workbook.

Copy and paste the VBA code shown below:

VBA Code:

Sub Get_Unique_Values1()Dim row As Longrow = Cells(Rows.Count, "C").End(xlUp).rowActiveSheet.Range("C5:C" & row).AdvancedFilter _Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("E5"), _Unique:=TrueEnd SubRun the code by clicking on the RubSub button or pressing the keyboard shortcut F5.

You’ll get the unique countries from the delivery country column.

VBA Code Explanation

Sub Get_Unique_Values1()

Sub indicates the name of our procedure Get_Unique_Values1().

Dim row As Long

The DIM statement in VBA refers to “declare” and it must be used to declare a variable. So, we declare a variable.

row = Cells(Rows.Count, "C").End(xlUp).row

We are using this line of code to count the number of rows until the last row. So count every row which contains values in column C.

ActiveSheet.Range("C5:C" & row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("E5"), Unique:=True

This line of code is for getting the unique values from column C and putting the result in column E.

End Sub

This line is for closing the procedure.

Method 2 – Excel VBA to Get Unique Values

Steps:

Open the Visual Basic Editor with Alt + F11.Go to Insert and select Module from the drop-down menu. This will open up the code module.Copy the following VBA code there.

VBA Code:

Sub Get_Unique_Values2()Set myRng = Range("C5:C14")Set r = Range("E5")myRng.AdvancedFilter Action:=xlFilterCopy, _CriteriaRange:=myRng, CopyToRange:=r, Unique:=TrueEnd SubPress the F5 key or click on the Run Sub button to run the code.

You will get the unique values as shown in the first method.

VBA Code Explanation

Set myRng = Range("C5:C14")Set r = Range("E5")

The VBA Set statement simply allows us to avoid having to type in the range we need to pick over and over again when running the code. So, we set our range to get the values from the range of cell C5:C14 and to put the unique values in column E.

myRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=myRng, CopyToRange:=r, Unique:=True

This line of code is for getting the unique values.

Method 3 – Extract Unique Values from Columns Using VBA in Excel

Steps:

Open the Visual Basic Editor with Alt + F11.Go to Insert and select Module from the drop-down menu. This will open up the code module.Copy the following VBA code there.

VBA Code:

Sub Get_Unique_Values3()Dim myArr As VariantDim rowC As LongWith Sheet9Sheets("Example3").Columns("C:C").AdvancedFilter _Action:=xlFilterCopy, CopyToRange:=.Range("E2"), Unique:=TruerowC = .Cells(.Rows.Count, "C").End(xlUp).rowmyArr = .Range("C3:C" & row)End WithDim myVal As StringDim a As IntegerFor a = 1 To UBound(myArr)myVal = myVal & myArr(a, 1) & ","NextEnd SubPress the F5 key to run the code or click on the Run Sub button.

You’ll get the unique values from the column shown in the first method.

VBA Code Explanation

Dim myArr As VariantDim rowC As Long

Declaring the variables.

With Sheet9

The With statement makes a sequence of statements on a single object without having to requalify the object’s name. As we work on Sheet9, we take the sheet number.

Sheets("Example3").Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("E2"), Unique:=True

This line of code is for getting the unique values from the column. First, we take the sheet name and then the column of this sheet from which we want to extract the unique values.

rowC = .Cells(.Rows.Count, "C").End(xlUp).row   myArr = .Range("C3:C" & row)

This line of code is for counting the total number of rows in that particular column, which has the value and putting those in an array.

Dim myVal As String   Dim a As Integer

Again we are declaring some variables for performing a loop.

For a = 1 To UBound(myArr)        myVal = myVal & myArr(a, 1) & ","  Next

Those lines of code will find the unique values one by one by performing a loop until the array does not end.

Method 4 – Run a VBA Macro to Extract Unique Values from a Column into a MsgBox

Steps:

Open the Visual Basic Editor with Alt + F11.Go to Insert and select Module from the drop-down menu. This will open up the code module.Copy the following VBA code there.

VBA Code:

Sub Get_Unique_Values4()mySheet = Sheets("Example4").Range("C5:C14")With CreateObject("scripting.dictionary")For Each myData In mySheeta = .Item(myData)NextMsgBox Join(.keys, vbLf)End WithEnd SubRun the code by pressing F5 on your keyboard.

You see the result in a Msgbox.

Download Practice Workbook

You can download the workbook and use it for practice.

Get Unique Values.xlsm

SaveSavedRemoved 0

Tags: Excel Unique Values

Sabrina Ayonclass="mb10">

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

6 Comments ReplyCYJul 28, 2022 at 12:01 AM

In your first example, Canada comes up twice. Same thing happens to me and I don’t know why.

ReplySabrina AyonAug 14, 2022 at 5:03 PM

Hello, CY!Yeah, actually this is because I set the cell first as “Range(“C5:C” & row)”. Here as I set cell C5, the element of the C5 cell will show up as the first unique value. You can use other VBA codes also if you want to get unique values with excel features, check this article- https://www.exceldemy.com/excel-unique-values-in-column/Hope this will help you!

ReplyMP RoyDec 15, 2022 at 8:27 PM

Hello Sabrina.

Thank you for explainning and sharing the code.

In a couple of the modules, I getrun-time error ‘1004’:Method ‘Range’ of object’_Worksheet’ failed

Any clue?

ReplyMusihaDec 19, 2022 at 12:27 PM

Hello MP ROY,Thank you for your comment. I have tried these codes and they are working perfectly, except the code in Example3. For that particular code, you can use a new workbook. While I was using a new workbook the code has been perfectly worked there. But you have to be careful about the name of worksheet. You have to use exact worksheet number and name in the code.Regards,MusihaTeam ExcelDemy

ReplyNIrmalMar 5, 2023 at 8:40 PM

Hi dear,Im Nirmal with you

I have some data with 13900 rows and 6 columns. column 1 showing Item name, among the items name some items repeated 2,3 or 4 times. I need to split this excel file which show row data of only unique item names and another sheets show data having duplicate barcode. is anyone can help me.

ReplyAdnan MasrufMar 9, 2023 at 11:10 AM

Dear Nirmal,Could you please provide me with your Excel file that you have mentioned or some screen shots from your dataset? That would help me to understand your dataset and answer your query accordingly.Thanks for your query.Regards,AdnanTeam ExcelDemy

Leave a reply Cancel reply